-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Insert]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Update]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Delete]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Load]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_HAIQUAN_QLHS_BienBan_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_SelectAll]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Insert]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@MaDoanhNghiep varchar(14),
	@PhanLuong int,
	@SoTo int,
	@TruongHop char(3),
	@MaHaiQuanChuyenDen char(6),
	@NoiGiao nvarchar(128),
	@NguoiGiao nvarchar(128),
	@NoiNhan nvarchar(128),
	@NguoiNhan nvarchar(128),
	@NgayBanGiao datetime,
	@CanBoTheoDoi varchar(128),
	@TrangThai varchar(8),
	@GhiChu nvarchar(1024),
	@SoBienBan nvarchar(16),
	@SoVanTaiDon nvarchar(128),
	@SoBanChinhToKhai int,
	@SoBanChinhDonChuyenCuaKhau int,
	@SoBanSaoVanTaiDon int,
	@SoBanChinhBanKeChiTiet int,
	@MatHoa nvarchar(512),
	@TinhTrangHangHoa nvarchar(512),
	@BienBanVeTinhTrangHangHoa nvarchar(512),
	@TuyenDuongVanChuyen nvarchar(512),
	@ChieuDai decimal(18, 2),
	@ThoiGianVanChuyen decimal(18, 2),
	@GioXuatPhat datetime,
	@CacVanDeLienQuan nvarchar(512),
	@NguoiNhapDuLieu varchar(128),
	@NgayNhapDuLieu datetime
AS
INSERT INTO [dbo].[t_HAIQUAN_QLHS_BienBan]
(
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[MaDoanhNghiep],
	[PhanLuong],
	[SoTo],
	[TruongHop],
	[MaHaiQuanChuyenDen],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[CanBoTheoDoi],
	[TrangThai],
	[GhiChu],
	[SoBienBan],
	[SoVanTaiDon],
	[SoBanChinhToKhai],
	[SoBanChinhDonChuyenCuaKhau],
	[SoBanSaoVanTaiDon],
	[SoBanChinhBanKeChiTiet],
	[MatHoa],
	[TinhTrangHangHoa],
	[BienBanVeTinhTrangHangHoa],
	[TuyenDuongVanChuyen],
	[ChieuDai],
	[ThoiGianVanChuyen],
	[GioXuatPhat],
	[CacVanDeLienQuan],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
)
VALUES
(
	@SoToKhai,
	@MaHaiQuan,
	@MaLoaiHinh,
	@NamDangKy,
	@MaDoanhNghiep,
	@PhanLuong,
	@SoTo,
	@TruongHop,
	@MaHaiQuanChuyenDen,
	@NoiGiao,
	@NguoiGiao,
	@NoiNhan,
	@NguoiNhan,
	@NgayBanGiao,
	@CanBoTheoDoi,
	@TrangThai,
	@GhiChu,
	@SoBienBan,
	@SoVanTaiDon,
	@SoBanChinhToKhai,
	@SoBanChinhDonChuyenCuaKhau,
	@SoBanSaoVanTaiDon,
	@SoBanChinhBanKeChiTiet,
	@MatHoa,
	@TinhTrangHangHoa,
	@BienBanVeTinhTrangHangHoa,
	@TuyenDuongVanChuyen,
	@ChieuDai,
	@ThoiGianVanChuyen,
	@GioXuatPhat,
	@CacVanDeLienQuan,
	@NguoiNhapDuLieu,
	@NgayNhapDuLieu
)

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Update]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@MaDoanhNghiep varchar(14),
	@PhanLuong int,
	@SoTo int,
	@TruongHop char(3),
	@MaHaiQuanChuyenDen char(6),
	@NoiGiao nvarchar(128),
	@NguoiGiao nvarchar(128),
	@NoiNhan nvarchar(128),
	@NguoiNhan nvarchar(128),
	@NgayBanGiao datetime,
	@CanBoTheoDoi varchar(128),
	@TrangThai varchar(8),
	@GhiChu nvarchar(1024),
	@SoBienBan nvarchar(16),
	@SoVanTaiDon nvarchar(128),
	@SoBanChinhToKhai int,
	@SoBanChinhDonChuyenCuaKhau int,
	@SoBanSaoVanTaiDon int,
	@SoBanChinhBanKeChiTiet int,
	@MatHoa nvarchar(512),
	@TinhTrangHangHoa nvarchar(512),
	@BienBanVeTinhTrangHangHoa nvarchar(512),
	@TuyenDuongVanChuyen nvarchar(512),
	@ChieuDai decimal(18, 2),
	@ThoiGianVanChuyen decimal(18, 2),
	@GioXuatPhat datetime,
	@CacVanDeLienQuan nvarchar(512),
	@NguoiNhapDuLieu varchar(128),
	@NgayNhapDuLieu datetime
AS

UPDATE
	[dbo].[t_HAIQUAN_QLHS_BienBan]
SET
	[MaDoanhNghiep] = @MaDoanhNghiep,
	[PhanLuong] = @PhanLuong,
	[SoTo] = @SoTo,
	[TruongHop] = @TruongHop,
	[MaHaiQuanChuyenDen] = @MaHaiQuanChuyenDen,
	[NoiGiao] = @NoiGiao,
	[NguoiGiao] = @NguoiGiao,
	[NoiNhan] = @NoiNhan,
	[NguoiNhan] = @NguoiNhan,
	[NgayBanGiao] = @NgayBanGiao,
	[CanBoTheoDoi] = @CanBoTheoDoi,
	[TrangThai] = @TrangThai,
	[GhiChu] = @GhiChu,
	[SoBienBan] = @SoBienBan,
	[SoVanTaiDon] = @SoVanTaiDon,
	[SoBanChinhToKhai] = @SoBanChinhToKhai,
	[SoBanChinhDonChuyenCuaKhau] = @SoBanChinhDonChuyenCuaKhau,
	[SoBanSaoVanTaiDon] = @SoBanSaoVanTaiDon,
	[SoBanChinhBanKeChiTiet] = @SoBanChinhBanKeChiTiet,
	[MatHoa] = @MatHoa,
	[TinhTrangHangHoa] = @TinhTrangHangHoa,
	[BienBanVeTinhTrangHangHoa] = @BienBanVeTinhTrangHangHoa,
	[TuyenDuongVanChuyen] = @TuyenDuongVanChuyen,
	[ChieuDai] = @ChieuDai,
	[ThoiGianVanChuyen] = @ThoiGianVanChuyen,
	[GioXuatPhat] = @GioXuatPhat,
	[CacVanDeLienQuan] = @CacVanDeLienQuan,
	[NguoiNhapDuLieu] = @NguoiNhapDuLieu,
	[NgayNhapDuLieu] = @NgayNhapDuLieu
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_InsertUpdate]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int,
	@MaDoanhNghiep varchar(14),
	@PhanLuong int,
	@SoTo int,
	@TruongHop char(3),
	@MaHaiQuanChuyenDen char(6),
	@NoiGiao nvarchar(128),
	@NguoiGiao nvarchar(128),
	@NoiNhan nvarchar(128),
	@NguoiNhan nvarchar(128),
	@NgayBanGiao datetime,
	@CanBoTheoDoi varchar(128),
	@TrangThai varchar(8),
	@GhiChu nvarchar(1024),
	@SoBienBan nvarchar(16),
	@SoVanTaiDon nvarchar(128),
	@SoBanChinhToKhai int,
	@SoBanChinhDonChuyenCuaKhau int,
	@SoBanSaoVanTaiDon int,
	@SoBanChinhBanKeChiTiet int,
	@MatHoa nvarchar(512),
	@TinhTrangHangHoa nvarchar(512),
	@BienBanVeTinhTrangHangHoa nvarchar(512),
	@TuyenDuongVanChuyen nvarchar(512),
	@ChieuDai decimal(18, 2),
	@ThoiGianVanChuyen decimal(18, 2),
	@GioXuatPhat datetime,
	@CacVanDeLienQuan nvarchar(512),
	@NguoiNhapDuLieu varchar(128),
	@NgayNhapDuLieu datetime
AS
IF EXISTS(SELECT [SoToKhai], [MaHaiQuan], [MaLoaiHinh], [NamDangKy] FROM [dbo].[t_HAIQUAN_QLHS_BienBan] WHERE [SoToKhai] = @SoToKhai AND [MaHaiQuan] = @MaHaiQuan AND [MaLoaiHinh] = @MaLoaiHinh AND [NamDangKy] = @NamDangKy)
	BEGIN
		UPDATE
			[dbo].[t_HAIQUAN_QLHS_BienBan] 
		SET
			[MaDoanhNghiep] = @MaDoanhNghiep,
			[PhanLuong] = @PhanLuong,
			[SoTo] = @SoTo,
			[TruongHop] = @TruongHop,
			[MaHaiQuanChuyenDen] = @MaHaiQuanChuyenDen,
			[NoiGiao] = @NoiGiao,
			[NguoiGiao] = @NguoiGiao,
			[NoiNhan] = @NoiNhan,
			[NguoiNhan] = @NguoiNhan,
			[NgayBanGiao] = @NgayBanGiao,
			[CanBoTheoDoi] = @CanBoTheoDoi,
			[TrangThai] = @TrangThai,
			[GhiChu] = @GhiChu,
			[SoBienBan] = @SoBienBan,
			[SoVanTaiDon] = @SoVanTaiDon,
			[SoBanChinhToKhai] = @SoBanChinhToKhai,
			[SoBanChinhDonChuyenCuaKhau] = @SoBanChinhDonChuyenCuaKhau,
			[SoBanSaoVanTaiDon] = @SoBanSaoVanTaiDon,
			[SoBanChinhBanKeChiTiet] = @SoBanChinhBanKeChiTiet,
			[MatHoa] = @MatHoa,
			[TinhTrangHangHoa] = @TinhTrangHangHoa,
			[BienBanVeTinhTrangHangHoa] = @BienBanVeTinhTrangHangHoa,
			[TuyenDuongVanChuyen] = @TuyenDuongVanChuyen,
			[ChieuDai] = @ChieuDai,
			[ThoiGianVanChuyen] = @ThoiGianVanChuyen,
			[GioXuatPhat] = @GioXuatPhat,
			[CacVanDeLienQuan] = @CacVanDeLienQuan,
			[NguoiNhapDuLieu] = @NguoiNhapDuLieu,
			[NgayNhapDuLieu] = @NgayNhapDuLieu
		WHERE
			[SoToKhai] = @SoToKhai
			AND [MaHaiQuan] = @MaHaiQuan
			AND [MaLoaiHinh] = @MaLoaiHinh
			AND [NamDangKy] = @NamDangKy
	END
ELSE
	BEGIN
	INSERT INTO [dbo].[t_HAIQUAN_QLHS_BienBan]
	(
			[SoToKhai],
			[MaHaiQuan],
			[MaLoaiHinh],
			[NamDangKy],
			[MaDoanhNghiep],
			[PhanLuong],
			[SoTo],
			[TruongHop],
			[MaHaiQuanChuyenDen],
			[NoiGiao],
			[NguoiGiao],
			[NoiNhan],
			[NguoiNhan],
			[NgayBanGiao],
			[CanBoTheoDoi],
			[TrangThai],
			[GhiChu],
			[SoBienBan],
			[SoVanTaiDon],
			[SoBanChinhToKhai],
			[SoBanChinhDonChuyenCuaKhau],
			[SoBanSaoVanTaiDon],
			[SoBanChinhBanKeChiTiet],
			[MatHoa],
			[TinhTrangHangHoa],
			[BienBanVeTinhTrangHangHoa],
			[TuyenDuongVanChuyen],
			[ChieuDai],
			[ThoiGianVanChuyen],
			[GioXuatPhat],
			[CacVanDeLienQuan],
			[NguoiNhapDuLieu],
			[NgayNhapDuLieu]
	)
	VALUES
	(
			@SoToKhai,
			@MaHaiQuan,
			@MaLoaiHinh,
			@NamDangKy,
			@MaDoanhNghiep,
			@PhanLuong,
			@SoTo,
			@TruongHop,
			@MaHaiQuanChuyenDen,
			@NoiGiao,
			@NguoiGiao,
			@NoiNhan,
			@NguoiNhan,
			@NgayBanGiao,
			@CanBoTheoDoi,
			@TrangThai,
			@GhiChu,
			@SoBienBan,
			@SoVanTaiDon,
			@SoBanChinhToKhai,
			@SoBanChinhDonChuyenCuaKhau,
			@SoBanSaoVanTaiDon,
			@SoBanChinhBanKeChiTiet,
			@MatHoa,
			@TinhTrangHangHoa,
			@BienBanVeTinhTrangHangHoa,
			@TuyenDuongVanChuyen,
			@ChieuDai,
			@ThoiGianVanChuyen,
			@GioXuatPhat,
			@CacVanDeLienQuan,
			@NguoiNhapDuLieu,
			@NgayNhapDuLieu
	)	
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Delete]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int
AS

DELETE FROM 
	[dbo].[t_HAIQUAN_QLHS_BienBan]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_DeleteDynamic]
	@WhereCondition nvarchar(500)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = 'DELETE FROM [dbo].[t_HAIQUAN_QLHS_BienBan] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_Load]
	@SoToKhai int,
	@MaHaiQuan char(6),
	@MaLoaiHinh char(5),
	@NamDangKy int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[MaDoanhNghiep],
	[PhanLuong],
	[SoTo],
	[TruongHop],
	[MaHaiQuanChuyenDen],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[CanBoTheoDoi],
	[TrangThai],
	[GhiChu],
	[SoBienBan],
	[SoVanTaiDon],
	[SoBanChinhToKhai],
	[SoBanChinhDonChuyenCuaKhau],
	[SoBanSaoVanTaiDon],
	[SoBanChinhBanKeChiTiet],
	[MatHoa],
	[TinhTrangHangHoa],
	[BienBanVeTinhTrangHangHoa],
	[TuyenDuongVanChuyen],
	[ChieuDai],
	[ThoiGianVanChuyen],
	[GioXuatPhat],
	[CacVanDeLienQuan],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
FROM
	[dbo].[t_HAIQUAN_QLHS_BienBan]
WHERE
	[SoToKhai] = @SoToKhai
	AND [MaHaiQuan] = @MaHaiQuan
	AND [MaLoaiHinh] = @MaLoaiHinh
	AND [NamDangKy] = @NamDangKy
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_SelectDynamic]
	@WhereCondition nvarchar(500),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = 
'SELECT 
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[MaDoanhNghiep],
	[PhanLuong],
	[SoTo],
	[TruongHop],
	[MaHaiQuanChuyenDen],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[CanBoTheoDoi],
	[TrangThai],
	[GhiChu],
	[SoBienBan],
	[SoVanTaiDon],
	[SoBanChinhToKhai],
	[SoBanChinhDonChuyenCuaKhau],
	[SoBanSaoVanTaiDon],
	[SoBanChinhBanKeChiTiet],
	[MatHoa],
	[TinhTrangHangHoa],
	[BienBanVeTinhTrangHangHoa],
	[TuyenDuongVanChuyen],
	[ChieuDai],
	[ThoiGianVanChuyen],
	[GioXuatPhat],
	[CacVanDeLienQuan],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
FROM [dbo].[t_HAIQUAN_QLHS_BienBan] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_HAIQUAN_QLHS_BienBan_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Tuesday, December 23, 2008
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_HAIQUAN_QLHS_BienBan_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[SoToKhai],
	[MaHaiQuan],
	[MaLoaiHinh],
	[NamDangKy],
	[MaDoanhNghiep],
	[PhanLuong],
	[SoTo],
	[TruongHop],
	[MaHaiQuanChuyenDen],
	[NoiGiao],
	[NguoiGiao],
	[NoiNhan],
	[NguoiNhan],
	[NgayBanGiao],
	[CanBoTheoDoi],
	[TrangThai],
	[GhiChu],
	[SoBienBan],
	[SoVanTaiDon],
	[SoBanChinhToKhai],
	[SoBanChinhDonChuyenCuaKhau],
	[SoBanSaoVanTaiDon],
	[SoBanChinhBanKeChiTiet],
	[MatHoa],
	[TinhTrangHangHoa],
	[BienBanVeTinhTrangHangHoa],
	[TuyenDuongVanChuyen],
	[ChieuDai],
	[ThoiGianVanChuyen],
	[GioXuatPhat],
	[CacVanDeLienQuan],
	[NguoiNhapDuLieu],
	[NgayNhapDuLieu]
FROM
	[dbo].[t_HAIQUAN_QLHS_BienBan]	

GO
